/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.ce.daoimplementation;

import com.bionic.ce.beans.User;
import com.bionic.ce.dao.IUserDAO;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class MySQLUserDAO extends MYSQLDAO implements IUserDAO {

    @Override
    public void add(User user) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO user "
                    + "(`Name`, `SurName`, `email`, `Password`, `Adress`, `Telephone`, `Status`) "
                    + "VALUES(?,?,?,?,?,?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setString(1, user.getName());
            ptmt.setString(2, user.getSurName());
            ptmt.setString(3, user.getEmail());
            ptmt.setString(4, user.getPassword());
            ptmt.setString(5, user.getAdress());
            ptmt.setString(6, user.getTelephone());
            ptmt.setString(7, user.getStatus());
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(User user) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE user SET `Name`=?, `SurName`=?, `email`=?, `Password`=?, `Adress`=?,"
                    + "`Telephone`=? WHERE `id`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setString(1, user.getName());
            ptmt.setString(2, user.getSurName());
            ptmt.setString(3, user.getEmail());
            ptmt.setString(4, user.getPassword());
            ptmt.setString(5, user.getAdress());
            ptmt.setString(6, user.getTelephone());
            ptmt.setInt(7, user.getUserid());
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteById(int id) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM user WHERE `id`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, id);
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<User> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<User> result = new ArrayList();
            String queryString = "SELECT * FROM user";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setUserid(resultSet.getInt("id"));
                user.setName(resultSet.getString("Name"));
                user.setSurName(resultSet.getString("SurName"));
                user.setEmail(resultSet.getString("email"));
                user.setPassword(resultSet.getString("Password"));
                user.setAdress(resultSet.getString("Adress"));
                user.setTelephone(resultSet.getString("Telephone"));
                user.setStatus(resultSet.getString("Status"));
                result.add(user);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<User> findByDynamicSelect(User user) throws SQLException {
        connection = getConnection();
        try {
            List<User> result = new ArrayList();
            String queryString = "SELECT * FROM user WHERE ";
            int i = 0;
            if (user.getUserid() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`id`=" + user.getUserid();
                i++;
            }
            if (user.getName() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Name`=\"" + user.getName() + "\"";
                i++;
            }
            if (user.getSurName() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`SurName`=\"" + user.getSurName() + "\"";
                i++;
            }
            if (user.getEmail() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`email`=\"" + user.getEmail() + "\"";
                i++;
            }
            if (user.getPassword() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Password`=\"" + user.getPassword() + "\"";
                i++;
            }
            if (user.getAdress() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Adress`=\"" + user.getAdress() + "\"";
                i++;
            }
            if (user.getTelephone() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Telephone`=\"" + user.getTelephone() + "\"";
                i++;
            }
            if (user.getStatus() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Status`=\"" + user.getStatus() + "\"";
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                user = new User();
                user.setUserid(resultSet.getInt("id"));
                user.setName(resultSet.getString("Name"));
                user.setSurName(resultSet.getString("SurName"));
                user.setEmail(resultSet.getString("email"));
                user.setPassword(resultSet.getString("Password"));
                user.setAdress(resultSet.getString("Adress"));
                user.setTelephone(resultSet.getString("Telephone"));
                user.setStatus(resultSet.getString("Status"));
                result.add(user);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }
}